package com.mairuide.phone.calladdress;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.mairuide._frame.base.BaseRepository;
import com.mairuide._frame.base.BaseServiceProxy;
import com.mairuide._frame.utils.Testtrans;
import com.mairuide.entity.PageResult;
import com.mairuide.httpclient.BaseSite;
import com.mairuide.phone.PhoneRecord;
import com.mairuide.phone.callanalysis.entity.PhoneMapDetail;
import com.mairuide.phone.import_phone.PhoneRecordingRepository;

/**
 * @Auther: zyj
 * @Date: 2018/12/19 09:35
 * @Description:
 */
@Service
@Transactional(readOnly = true)
public class AddressStatisticsService extends BaseServiceProxy<PhoneRecord> {

    @Autowired
    private PhoneRecordingRepository phoneRecordingRepository;
    @PersistenceContext
    private EntityManager entityManager;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Resource
    private  BaseSite  baseSite;

    @Override
    protected BaseRepository<PhoneRecord> getBaseRepository() {
        return phoneRecordingRepository;
    }

    /**
     *疑是居住地分析-当天早晚
     * @return
     */
    public List <MayBeAddr> residenceStatistics(PhoneAddressDto dto){
        String serviceNumber = dto.getCardList();
        String frequency = dto.getFrequency();
        String minTime = dto.getStartTime();
        String maxTime = dto.getEndTime();
        String wanshang = dto.getNight();
        String zaoshang = dto.getMorning();
        String caseId = dto.getCaseId();
        String order = dto.getOrder();
        String sort = dto.getSort();
        String sql="SELECT min(STARTING_TIME) startTime, max(STARTING_TIME) endTime,lac,cid,lngb,latb,SERVICE_NUMBER serviceNumber,address,count(1) count,sum(abs(TALK_TIME)) talkTime FROM TB_PHONE_RECORDING ";
        sql+=" WHERE is_delete = 0  AND CALL_TYPE IN ( 0, 1 )  and lac is not null and cid is not null ";
        if (StringUtils.isNotBlank(caseId)){
            sql+=" and casemanage_id = '" + caseId +"' ";
        }
        if (StringUtils.isNotBlank(serviceNumber)){
            sql+=" and SERVICE_NUMBER IN (" + serviceNumber +")";
        }else{
            sql+=" and 1=0 ";
        }
        if (StringUtils.isNotBlank(minTime)){
            sql+=" and TO_CHAR(STARTING_TIME,'yyyy-mm-dd')>='"+minTime+"'";
        }
        if (StringUtils.isNotBlank(maxTime)){
            sql+=" and TO_CHAR(STARTING_TIME,'yyyy-mm-dd')<='"+maxTime+"'";
        }
        if (StringUtils.isNotBlank(zaoshang)&&StringUtils.isNotBlank(wanshang)){
            sql+="AND ( to_char( STARTING_TIME, 'hh24' ) < '"+zaoshang+"' OR to_char( STARTING_TIME, 'hh24' ) >= '"+wanshang+"' ) ";
        }
        sql+=" GROUP BY SERVICE_NUMBER,ADDRESS,lac,cid,LNGB,LATB ";
        if (dto.getType()!=null&&dto.getType()==0){
            sql+=" ,trunc(STARTING_TIME, 'DD') ";
        }
        if (StringUtils.isNotBlank(frequency)){
            sql+=" HAVING count(1)>= "+frequency;
        }else {
            sql+=" HAVING count(1)>= 2";
        }
        if (StringUtils.isNotBlank(sort)&& StringUtils.isNotBlank(order)){
            sql+=" ORDER BY " + sort + " " + order;
        }else{
            sql+=" ORDER BY count DESC ";
        }
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(MayBeAddr.class));
        List<MayBeAddr> maps = query.getResultList();
        // 设置分页
//        query.setFirstResult((dto.getPage() - 1) * dto.getRows());
//        query.setMaxResults(dto.getRows());
//        // 获取查询结果集
//        List<MayBeAddr> list = query.getResultList();

//        PageResult pageResult = new PageResult(Long.valueOf(maps.size()), maps);

        return groupServiceLacCid(maps);
    }

    public List <MayBeAddr> groupServiceLacCid(List <MayBeAddr> content){
        Map<String,List<MayBeAddr>> map = new HashMap<>();
        for (MayBeAddr p:content){
            if (map.containsKey(p.getServiceNumber()+"_"+p.getLac()+"_"+p.getCid())){
                List<MayBeAddr> list = map.get(p.getServiceNumber()+"_"+p.getLac()+"_"+p.getCid());
                list.add(p);
            }else {
                List<MayBeAddr> list = new ArrayList<>();
                list.add(p);
                map.put(p.getServiceNumber()+"_"+p.getLac()+"_"+p.getCid(),list);
            }
        }
        List <MayBeAddr> resultList = new ArrayList<>();
        Set<String> set = map.keySet();
        for (String s:set){
            List<MayBeAddr> list = map.get(s);
            if (list.size()==1){
                resultList.add(list.get(0));
            } else {
                MayBeAddr mb = new MayBeAddr();
                MayBeAddr mayBeAddr = list.get(0);
                mb.setAddress(mayBeAddr.getAddress());
                mb.setCid(mayBeAddr.getCid());
                mb.setLac(mayBeAddr.getLac());
                mb.setLatb(mayBeAddr.getLatb());
                mb.setServiceNumber(mayBeAddr.getServiceNumber());
                mb.setLngb(mayBeAddr.getLngb());
                MayBeAddr startTime = list.stream().min(Comparator.comparing(MayBeAddr::getStartTime)).get();
                mb.setStartTime(startTime.getStartTime());
                MayBeAddr endTime = list.stream().max(Comparator.comparing(MayBeAddr::getEndTime)).get();
                mb.setEndTime(endTime.getEndTime());
                int count = list.stream().mapToInt(m -> m.getCount()).sum();
                mb.setCount(count);
                int talk = list.stream().mapToInt(m -> m.getTalkTime()).sum();
                mb.setTalkTime(talk);
                resultList.add(mb);
            }
        }
        resultList.sort((o1, o2) -> o2.getCount().compareTo(o1.getCount()));
        return resultList;
    }

    /**
     * 疑是居住地分析-隔天早晚
     * @param dto
     * @return
     */
    public List <MayBeAddr>  residenceStatisticsGeTian(PhoneAddressDto dto){
        List <MayBeAddr> list = new ArrayList<>();
        String afterSql = "select to_char(t.STARTING_TIME+15/24, 'YYYY-MM-DD') as startingTime from TB_PHONE_RECORDING t WHERE is_delete = 0 AND CALL_TYPE IN ( 0, 1 ) ";
        if (StringUtils.isNotBlank(dto.getCaseId())){
            afterSql+=" and t.casemanage_id = '" + dto.getCaseId() +"' ";
        }
        if (StringUtils.isNotBlank(dto.getCardList())){
            afterSql+=" and t.SERVICE_NUMBER IN (" + dto.getCardList() +")";
        }else{
            return new ArrayList<MayBeAddr>();
        }
        afterSql += " group by to_char(t.STARTING_TIME+15/24, 'YYYY-MM-DD') order by startingTime";
        List <String> timeList = jdbcTemplate.queryForList(afterSql, String.class);
        SimpleDateFormat f =  new SimpleDateFormat("yyyy-MM-dd");
        for (int i=0;i<timeList.size()-1;i++){
            String format = null;
            try {
                Date parse = f.parse(timeList.get(i));
                format = f.format(parse.getTime() + 24 * 3600 * 1000);
            } catch (ParseException e) {
                e.printStackTrace();
                continue;
            }
            String sql="SELECT min(STARTING_TIME) startTime, max(STARTING_TIME) endTime,lac,cid,lngb,latb,SERVICE_NUMBER serviceNumber,address,count(1) count,sum(abs(TALK_TIME)) talkTime FROM TB_PHONE_RECORDING WHERE is_delete = 0  AND CALL_TYPE IN ( 0, 1 ) and lac is not null and cid is not null ";
            if (StringUtils.isNotBlank(dto.getCaseId())){
                sql+=" and casemanage_id = '" + dto.getCaseId() +"' ";
            }
            if (StringUtils.isNotBlank(dto.getCardList())){
                sql+=" and SERVICE_NUMBER IN (" + dto.getCardList() +")";
            }else{
                sql+=" and 1=0 ";
            }
            sql += " AND STARTING_TIME >= to_date('"+timeList.get(i)+" 12:00:00','yyyy-mm-dd hh24:mi:ss') AND STARTING_TIME <= to_date('"+format+" 12:00:00','yyyy-mm-dd hh24:mi:ss') ";
            if (StringUtils.isNotBlank(dto.getMorning())&&StringUtils.isNotBlank(dto.getNight())){
                sql+="AND ( to_char( STARTING_TIME, 'hh24' ) < '"+dto.getMorning()+"' OR to_char( STARTING_TIME, 'hh24' ) >= '"+dto.getNight()+"' ) ";
            }
            sql+=" GROUP BY SERVICE_NUMBER,ADDRESS,lac,cid,LNGB,LATB ";
            if (StringUtils.isNotBlank(dto.getFrequency())){
                sql+=" HAVING count(1)>= "+dto.getFrequency();
            }else {
                sql+=" HAVING count(1)>= 2";
            }
            list.addAll(jdbcTemplate.query(sql, new BeanPropertyRowMapper<MayBeAddr>(MayBeAddr.class)));
        }

        return groupServiceLacCid(list);
    }

    /**
     *疑是居住地分析地图分析
     * @return
     */
    public Map residenceStatisticsMap(PhoneAddressDto dto){
        ArrayList<PhoneMapDetail> phoneMapDetailList = new ArrayList<>();
        String lngb = dto.getLngb()==null?"0":dto.getLngb();
        String latb = dto.getLatb()==null?"0":dto.getLatb();
        String address = dto.getAddress()==null?"0":dto.getAddress();
        String cid = dto.getCid()==null?"0":dto.getCid();
        String lac = dto.getLac()==null?"0":dto.getLac();

        PhoneMapDetail mapDetail = new PhoneMapDetail();
        mapDetail.setLNGB(lngb);
        mapDetail.setLATB(latb);
        mapDetail.setADDRESS(address);
        mapDetail.setLAC(lac);
        mapDetail.setCID(cid);
        mapDetail.setSERVICE_NUMBER(dto.getCardList());
        phoneMapDetailList.add(mapDetail);
        List arrayList_hava = baseSite.getMap(phoneMapDetailList);
        Map<Object, Object> map = new HashMap<>();
        map.put("data",arrayList_hava);
        return map;
    }

    /**
     * 工作地分析
     * 通过分析上班时间接打电话次数和基站位置判断对方的疑似工作地
     * @return
     */
    public PageResult workplaceStatistics(PhoneAddress pa,Integer page,Integer size){
        StringBuffer sb = new StringBuffer();
        sb.append(
                "SELECT\n" +
                "	MAX( address ) AS address,\n" +
                "	SERVICE_NUMBER,\n" +
                "	LAC,\n" +
                "	CID,\n" +
                "	MAX(LATB) AS LATB,\n" +
                "	MAX(LNGB) AS LNGB,\n" +
                "	COUNT( CASE WHEN to_char( STARTING_TIME, 'd' )=2 THEN 1 END ) AS monday,\n" +
                "	COUNT( CASE WHEN to_char( STARTING_TIME, 'd' )=3 THEN 1 END ) AS Tuesday,\n" +
                "	COUNT( CASE WHEN to_char( STARTING_TIME, 'd' )=4 THEN 1 END ) AS Wednesday,\n" +
                "	COUNT( CASE WHEN to_char( STARTING_TIME, 'd' )=5 THEN 1 END ) AS Thursday,\n" +
                "	COUNT( CASE WHEN to_char( STARTING_TIME, 'd' )=6 THEN 1 END ) AS Friday,\n" +
                "  COUNT(*) count\n" +
                "from TB_PHONE_RECORDING tpr\n" +
                "where is_delete=0 and casemanage_id = '"+pa.getCaseId()+"'\n"+
                "  AND to_char(STARTING_TIME,'d') in (2,3,4,5,6)\n" +
                " AND to_char(STARTING_TIME,'hh24')>='"+pa.getMorning()+"' AND to_char(STARTING_TIME,'hh24')<'"+pa.getNight()+"'\n");
        if (StringUtils.isNotBlank(pa.getPartyNumber())){
            sb.append(" AND PARTY_NUMBER = '"+pa.getPartyNumber()+"'");
        }
        if (StringUtils.isNotBlank(pa.getCardList())){
            sb.append(" and SERVICE_NUMBER in ("+pa.getCardList()+")");
        }else {
            sb.append(" and 1=0 ");
        }
        if (StringUtils.isNotBlank(pa.getStartTime())){
            sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')>='"+pa.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(pa.getEndTime())){
            sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')<='"+pa.getEndTime()+"'");
        }
        sb.append(" GROUP BY SERVICE_NUMBER,LAC,CID");
        if (StringUtils.isNotBlank(pa.getFrequency())){
            sb.append(" having count(*)>="+pa.getFrequency());
        }
        if (StringUtils.isNotBlank(pa.getSort())){
            sb.append(" order by "+pa.getSort()+" " +pa.getOrder());
        }else {
            sb.append(" ORDER BY count desc");
        }
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
        Query query = entityManager.createNativeQuery(sb.toString());
        //将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        //设置分页
        query.setFirstResult((page-1)*size);
        query.setMaxResults(size);
        List<Map<String, Object>> list = query.getResultList();
        
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),list);
        return pageResult;
    }
    
    public PageResult working_map(PhoneAddress pa) {
    	StringBuffer sb=new StringBuffer();
        sb.append(" SELECT RECORD_ID,nvl(CID,'0') CID ,nvl(LAC,'0') LAC,SERVICE_NUMBER,nvl(LNGB,'0') LNGB,nvl(LATB,'0') LATB,nvl(ADDRESS ,'0') ADDRESS,to_char(STARTING_TIME,'yyyy-mm-dd hh24:mi:ss') STARTING_TIME,PARTY_NUMBER,CALL_TYPE,TALK_TIME ");
        sb.append(" from TB_PHONE_RECORDING WHERE is_delete=0 and casemanage_id = '"+pa.getCaseId()+"'\n"+
        		" AND to_char(STARTING_TIME,'d') in (2,3,4,5,6)\n"+
        		" AND to_char(STARTING_TIME,'hh24')>='"+pa.getMorning()+"' AND to_char(STARTING_TIME,'hh24')<='"+pa.getNight()+"'\n");
        if (StringUtils.isNotBlank(pa.getPartyNumber())){
        	sb.append(" AND PARTY_NUMBER = '"+pa.getPartyNumber()+"'");
        }
        if (StringUtils.isNotBlank(pa.getCardList())){
        	sb.append(" and SERVICE_NUMBER in ("+pa.getCardList()+")");
        }else {
        	sb.append(" and 1=0 ");
        }
        if (StringUtils.isNotBlank(pa.getStartTime())){
        	sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')>='"+pa.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(pa.getEndTime())){
        	sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')<='"+pa.getEndTime()+"'");
        }
        
        sb.append(" ORDER BY STARTING_TIME DESC");
        List<PhoneMapDetail> maps = jdbcTemplate.query(sb.toString(),new BeanPropertyRowMapper<PhoneMapDetail>(PhoneMapDetail.class));
        //封装地图数据
        List arrayList_hava = baseSite.getMap(maps);
        HashMap<String, Object> result = new HashMap<>();
        result.put("data",arrayList_hava);
        
        List<PhoneMapDetail> resultList = maps;
        for(PhoneMapDetail pd : (List<PhoneMapDetail>) arrayList_hava) {
        	for(PhoneMapDetail pd2 :resultList) {
        		if(!pd2.getCID().equals("0") && !pd2.getLAC().equals("0")) {
        			if(pd.getCID().equals(pd2.getCID()) && pd.getLAC().equals(pd2.getLAC())) {
        				pd2.setADDRESS(pd.getADDRESS());
        			}
        		}
        	}
        }
        result.put("data2",resultList);
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),null,result);
        return pageResult;
    }

    /**
     * 双休日分析
     * 通过分析双休日的接打电话次数和基站位置判断对方工作外的活动
     * @return
     */
    public PageResult workedStatistics(PhoneAddress pa,Integer page,Integer size){
        StringBuffer sb = new StringBuffer();
        sb.append(
                "SELECT\n" +
                "	MAX( address ) AS address,\n" +
                "	SERVICE_NUMBER,\n" +
                "	LAC,\n" +
                "	CID,\n" +
                "	MAX(LATB) AS LATB,\n" +
                "	MAX(LNGB) AS LNGB,\n" +
                "	COUNT( CASE WHEN to_char( STARTING_TIME, 'd' )=1 THEN 1 END ) AS Sunday,\n" +
                "	COUNT( CASE WHEN to_char( STARTING_TIME, 'd' )=7 THEN 1 END ) AS Saturday,\n" +
                "  COUNT(*) count\n" +
                "from TB_PHONE_RECORDING tpr\n" +
                "where is_delete=0 and casemanage_id = '"+pa.getCaseId()+"'\n" +
                "  AND to_char(STARTING_TIME,'d') in (1,7)");
        if (StringUtils.isNotBlank(pa.getPartyNumber())){
            sb.append(" AND PARTY_NUMBER = '"+pa.getPartyNumber()+"'");
        }
        if (StringUtils.isNotBlank(pa.getCardList())){
            sb.append(" and SERVICE_NUMBER in ("+pa.getCardList()+")");
        }else {
            sb.append(" and 1=0 ");
        }
        if (StringUtils.isNotBlank(pa.getStartTime())){
            sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')>='"+pa.getStartTime()+"'");
            sb.append("");
        }
        if (StringUtils.isNotBlank(pa.getEndTime())){
            sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')<='"+pa.getEndTime()+"'");
        }
        sb.append(" GROUP BY SERVICE_NUMBER,LAC,CID");
        if (StringUtils.isNotBlank(pa.getFrequency())){
            sb.append(" having count(*)>="+pa.getFrequency());
        }
        if (StringUtils.isNotBlank(pa.getSort())){
            sb.append(" order by "+pa.getSort()+" " +pa.getOrder());
        }else {
            sb.append(" ORDER BY count desc");
        }
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
        Query query = entityManager.createNativeQuery(sb.toString());
        //将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        //设置分页
        query.setFirstResult((page-1)*size);
        query.setMaxResults(size);
        //获取查询结果集
        List<Map<String, Object>> list = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),list,null);
        return pageResult;

    }
    
    public PageResult worked_map(PhoneAddress pa) {
    	StringBuffer sb=new StringBuffer();
    	sb.append(" SELECT RECORD_ID,nvl(CID,'0') CID ,nvl(LAC,'0') LAC,SERVICE_NUMBER,nvl(LNGB,'0') LNGB,nvl(LATB,'0') LATB,nvl(ADDRESS ,'0') ADDRESS,to_char(STARTING_TIME,'yyyy-mm-dd hh24:mi:ss') STARTING_TIME,PARTY_NUMBER,CALL_TYPE,TALK_TIME ");
    	sb.append(" from TB_PHONE_RECORDING WHERE is_delete=0 and casemanage_id = '"+pa.getCaseId()+"'\n"+
    			" AND to_char(STARTING_TIME,'d') in (1,7)\n");
    	if (StringUtils.isNotBlank(pa.getPartyNumber())){
    		sb.append(" AND PARTY_NUMBER = '"+pa.getPartyNumber()+"'");
    	}
    	if (StringUtils.isNotBlank(pa.getCardList())){
    		sb.append(" and SERVICE_NUMBER in ("+pa.getCardList()+")");
    	}else {
    		sb.append(" and 1=0 ");
    	}
    	if (StringUtils.isNotBlank(pa.getStartTime())){
    		sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')>='"+pa.getStartTime()+"'");
    	}
    	if (StringUtils.isNotBlank(pa.getEndTime())){
    		sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')<='"+pa.getEndTime()+"'");
    	}
    	
    	sb.append(" ORDER BY STARTING_TIME DESC");
    	List<PhoneMapDetail> maps = jdbcTemplate.query(sb.toString(),new BeanPropertyRowMapper<PhoneMapDetail>(PhoneMapDetail.class));
    	//封装地图数据
    	List arrayList_hava = baseSite.getMap(maps);
    	HashMap<String, Object> result = new HashMap<>();
    	result.put("data",arrayList_hava);
    	
    	List<PhoneMapDetail> resultList = maps;
    	for(PhoneMapDetail pd : (List<PhoneMapDetail>) arrayList_hava) {
    		for(PhoneMapDetail pd2 :resultList) {
    			if(!pd2.getCID().equals("0") && !pd2.getLAC().equals("0")) {
    				if(pd.getCID().equals(pd2.getCID()) && pd.getLAC().equals(pd2.getLAC())) {
    					pd2.setADDRESS(pd.getADDRESS());
    				}
    			}
    		}
    	}
    	result.put("data2",resultList);
    	PageResult pageResult = new PageResult(Long.valueOf(maps.size()),null,result);
    	return pageResult;
    }

    /**
     * 特殊日期分析
     * @return
     */
    public PageResult specialDatesStatistics(PhoneAddress pa,Integer page,Integer size){
        StringBuffer sb = new StringBuffer();
        sb.append(
                "select\n" +
                        "  MAX(address) as BASE_STATION,\n" +
                        "  SERVICE_NUMBER,\n" +
                        "  LAC,\n" +
                        "  CID,\n"+
                        "  trim(both ',' from sys.stragg(to_char(PARTY_NUMBER) || nvl2(PARTY_NUMBER, ',', ''))) as  PARTY_NUMBER,\n" +
                        "  COUNT(*) count\n" +
                        "from TB_PHONE_RECORDING tpr\n" +
                        "where is_delete=0 and casemanage_id = '"+pa.getCaseId()+"'");
        if (StringUtils.isNotBlank(pa.getPartyNumber())){
            sb.append(" AND PARTY_NUMBER = '"+pa.getPartyNumber()+"'");
        }
        if (StringUtils.isNotBlank(pa.getCardList())){
            sb.append(" and SERVICE_NUMBER in ("+pa.getCardList()+")");
        }else {
            sb.append(" and 1=0 ");
        }
        if (StringUtils.isNotBlank(pa.getStartTime())){
            sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')>='"+pa.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(pa.getEndTime())){
            sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')<='"+pa.getEndTime()+"'");
        }
        sb.append(" GROUP BY SERVICE_NUMBER,LAC,CID");
        if (StringUtils.isNotBlank(pa.getFrequency())){
            sb.append(" having count(*)>="+pa.getFrequency());
        }
        if (StringUtils.isNotBlank(pa.getSort())){
            sb.append(" order by "+pa.getSort()+" " +pa.getOrder());
        }else {
            sb.append(" ORDER BY count desc");
        }
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
        Query query = entityManager.createNativeQuery(sb.toString());
        //将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        //设置分页
        query.setFirstResult((page-1)*size);
        query.setMaxResults(size);
        //获取查询结果集
        List<Map<String, Object>> list = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),list,null);
        return pageResult;
    }
    
    public PageResult specialDate_map(PhoneAddress pa) {
    	StringBuffer sb=new StringBuffer();
    	sb.append(" SELECT RECORD_ID,nvl(CID,'0') CID ,nvl(LAC,'0') LAC,SERVICE_NUMBER,nvl(LNGB,'0') LNGB,nvl(LATB,'0') LATB,nvl(ADDRESS ,'0') ADDRESS,to_char(STARTING_TIME,'yyyy-mm-dd hh24:mi:ss') STARTING_TIME,PARTY_NUMBER,CALL_TYPE,TALK_TIME ");
    	sb.append(" from TB_PHONE_RECORDING WHERE is_delete=0 and casemanage_id = '"+pa.getCaseId()+"'\n");
    	if (StringUtils.isNotBlank(pa.getPartyNumber())){
    		sb.append(" AND PARTY_NUMBER = '"+pa.getPartyNumber()+"'");
    	}
    	if (StringUtils.isNotBlank(pa.getCardList())){
    		sb.append(" and SERVICE_NUMBER in ("+pa.getCardList()+")");
    	}else {
    		sb.append(" and 1=0 ");
    	}
    	if (StringUtils.isNotBlank(pa.getStartTime())){
    		sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')>='"+pa.getStartTime()+"'");
    	}
    	if (StringUtils.isNotBlank(pa.getEndTime())){
    		sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')<='"+pa.getEndTime()+"'");
    	}
    	
    	sb.append(" ORDER BY STARTING_TIME DESC");
    	List<PhoneMapDetail> maps = jdbcTemplate.query(sb.toString(),new BeanPropertyRowMapper<PhoneMapDetail>(PhoneMapDetail.class));
    	//封装地图数据
    	List arrayList_hava = baseSite.getMap(maps);
    	HashMap<String, Object> result = new HashMap<>();
    	result.put("data",arrayList_hava);
    	
    	List<PhoneMapDetail> resultList = maps;
    	for(PhoneMapDetail pd : (List<PhoneMapDetail>) arrayList_hava) {
    		for(PhoneMapDetail pd2 :resultList) {
    			if(!pd2.getCID().equals("0") && !pd2.getLAC().equals("0")) {
    				if(pd.getCID().equals(pd2.getCID()) && pd.getLAC().equals(pd2.getLAC())) {
    					pd2.setADDRESS(pd.getADDRESS());
    				}
    			}
    		}
    	}
    	result.put("data2",resultList);
    	PageResult pageResult = new PageResult(Long.valueOf(maps.size()),null,result);
    	return pageResult;
    }

    /**
     * 详情展示
     *
     * @return
     */
    public PageResult details(PhoneAddress pa ,Integer page,Integer size){
        StringBuffer sb = new StringBuffer();
        sb.append(
                "select * from TB_PHONE_RECORDING tpr\n" +
                        "where is_delete=0 and casemanage_id = '"+pa.getCaseId()+"'");
        sb.append(" AND SERVICE_NUMBER = '"+pa.getServiceNumber()+"'");
        if (!"null".equals(pa.getLac())){
            sb.append(" AND lac = '"+pa.getLac()+"'");
        }else {
            sb.append(" AND lac is null");
        }
        if (!"null".equals(pa.getCid())){
            sb.append(" AND cid = '"+pa.getCid()+"'");
        }else {
            sb.append(" AND cid is null");
        }
        if (StringUtils.isNotBlank(pa.getPartyNumber())){
            sb.append(" AND PARTY_NUMBER = '"+pa.getPartyNumber()+"'");
        }
        if (StringUtils.isNotBlank(pa.getAddress())){
            sb.append(" AND address = '"+pa.getAddress()+"'");
        }
        if (StringUtils.isNotBlank(pa.getStartDay())||StringUtils.isNotBlank(pa.getEndDay())){
//            if (pa.getStartDay().equals(pa.getEndDay())){
                sb.append("  AND (to_char(STARTING_TIME,'yyyy-mm-dd')>='"+pa.getStartDay()+"' or to_char(STARTING_TIME,'yyyy-mm-dd')<='"+pa.getEndDay()+"') ");
//            }else {
//                sb.append(" AND STARTING_TIME >= to_date('"+pa.getStartDay()+" 12:00:00','yyyy-mm-dd hh24:mi:ss') AND STARTING_TIME <= to_date('"+pa.getEndDay()+" 12:00:00','yyyy-mm-dd hh24:mi:ss') ");
//            }
        }else {
            if (StringUtils.isNotBlank(pa.getStartTime())){
                sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')>='"+pa.getStartTime()+"'");
            }
            if (StringUtils.isNotBlank(pa.getEndTime())){
                sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')<='"+pa.getEndTime()+"'");
            }
        }
        if(StringUtils.isNotBlank(pa.getWork())){
            sb.append("  AND to_char(STARTING_TIME,'d') in ("+pa.getWork()+")");
            if (StringUtils.isNotBlank(pa.getMorning())) {
                sb.append(" AND to_char(STARTING_TIME,'hh24')>='" + pa.getMorning() + "' AND to_char(STARTING_TIME,'hh24')<'" + pa.getNight() + "'");
            }
        } else {
            if (StringUtils.isNotBlank(pa.getMorning())){
                sb.append("  AND (to_char(STARTING_TIME,'hh24')<'"+pa.getMorning()+"' or to_char(STARTING_TIME,'hh24')>='"+pa.getNight()+"')");
            }
        }
        if (StringUtils.isNotBlank(pa.getSort())){
            sb.append(" order by "+pa.getSort()+" "+ pa.getOrder());
        }

        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
        Query query = entityManager.createNativeQuery(sb.toString());
        //将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        //设置分页
        query.setFirstResult((page-1)*size);
        query.setMaxResults(size);
        //获取查询结果集
        List<Map<String, Object>> list = query.getResultList();
        getSite(list);//获取基站信息
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),list,maps);

        return pageResult;

    }

    //D3关系查询
    public List<Map<String, Object>> getPhoneD3(PhoneAddress pa){
        StringBuffer sb = new StringBuffer();
        sb.append(
                "select SERVICE_NUMBER,count(*) frequency,BASE_STATION,\n" +
                "  trim(both ',' from sys.stragg(to_char(PARTY_NUMBER) || nvl2(PARTY_NUMBER, ',', ''))) as  PARTY_NUMBER \n" +
                " from TB_PHONE_RECORDING tpr\n" +
                " where is_delete=0 and casemanage_id = '"+pa.getCaseId()+"'");
        if (StringUtils.isNotBlank(pa.getBaseStation())){
            sb.append(" AND BASE_STATION ='"+pa.getBaseStation()+"'");
        }
        if (StringUtils.isNotBlank(pa.getCardList())){
            sb.append(" and SERVICE_NUMBER in ("+pa.getCardList()+")");
        }else {
            sb.append(" and 1=0 ");
        }
        if (StringUtils.isNotBlank(pa.getPartyNumber())){
            sb.append(" AND PARTY_NUMBER = '"+pa.getPartyNumber()+"'");
        }
        if (StringUtils.isNotBlank(pa.getStartTime())){
            sb.append(" AND to_char(STARTING_TIME,'yyyy-mm-dd')>='"+pa.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(pa.getEndTime())){
            sb.append("  AND to_char(STARTING_TIME,'yyyy-mm-dd')<='"+pa.getEndTime()+"'");
        }
        if("working".equals(pa.getWork())){
            sb.append("  AND to_char(STARTING_TIME,'d') in (2,3,4,5,6)");
            sb.append( " AND to_char(STARTING_TIME,'hh24')>='"+pa.getMorning()+"' AND to_char(STARTING_TIME,'hh24')<='"+pa.getNight()+"'");
        }else if ("worked".equals(pa.getWork())){
            sb.append("  AND to_char(STARTING_TIME,'d') in (1,7)");
        }else {
            if (StringUtils.isNotBlank(pa.getMorning())){
                sb.append("  AND (to_char(STARTING_TIME,'hh24')<='"+pa.getMorning()+"' or to_char(STARTING_TIME,'hh24')>='"+pa.getNight()+"')");
            }
        }
        sb.append(" GROUP BY SERVICE_NUMBER,BASE_STATION");
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());

        List<Map<String, Object>> list = new ArrayList<>();
        if(maps.size()>0){
            String cardList = pa.getCardList();
            String[] split = cardList.split(",");
            if (split.length>0) {
                for (String s : split) {
                    Map<String, Object> bodyMap = new HashMap();
                    ArrayList children = new ArrayList<>();
                    for (int i = 0; i < maps.size(); i++) {
                        Map<Object, Object> map1 = new HashMap<>();
                        Map<String, Object> map = maps.get(i);
                        map1.put("id", UUID.randomUUID());
                        map1.put("name", map.get("PARTY_NUMBER"));
                        map1.put("size", 3938);
                        map1.put("width", 60);
                        map1.put("height", 60);
                        map1.put("image", "/plugins/d3/image/ka2.png");
                        map1.put("parentlinename", "通话" + map.get("FREQUENCY") + "次");
                        children.add(map1);
                    }

                    bodyMap.put("id", UUID.randomUUID());
                    bodyMap.put("name", s);
                    bodyMap.put("image", "/plugins/d3/image/body2.png");
                    bodyMap.put("width", 100);
                    bodyMap.put("height", 100);
                    bodyMap.put("children", children);

                    list.add(bodyMap);
                }
            }
        }
        return list;
    }

    public Map getMap(List<Map<String, Object>> maps){
        Map callInfoByIds = new HashMap();
        try {
            callInfoByIds = baseSite.getCallInfoByIds(BaseSite.getBaseSiteIds(maps));
            if(String.valueOf(callInfoByIds.get("code")).equals("20000")){
                List<String> baseSiteList = BaseSite.getBaseSiteList(maps);
                List<Map<String,Object>> data = (List<Map<String,Object>>)callInfoByIds.get("data");
                ArrayList<Map<String,Object>> list1 = new ArrayList<>();
                if (data!=null){
                    for (Map<String,Object> m:data){
                        list1.add(maps.get(baseSiteList.indexOf((String) m.get("id"))));
                    }
                }
                callInfoByIds.put("data2",list1);
            }else{
                callInfoByIds.put("data2",null);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return callInfoByIds;
    }

    private void getSite(List<Map<String, Object>> list) {
   	 Set<PhoneMapDetail> phoneMapDetailList = new HashSet<>();
        if(list!=null && list!=null) {
			for(Map<String, Object> pr:list) {
				if(		StringUtils.isNotEmpty(pr.get("SERVICE_NUMBER")==null?"":pr.get("SERVICE_NUMBER").toString()) 
						&& StringUtils.isEmpty(pr.get("ADDRESS")==null?"":pr.get("ADDRESS").toString()) 
						&& StringUtils.isNotEmpty(pr.get("CID")==null?"":pr.get("CID").toString()) 
						&& StringUtils.isNotEmpty(pr.get("LAC")==null?"":pr.get("LAC").toString())) {
					PhoneMapDetail detail = new PhoneMapDetail();
					detail.setCID(StringUtils.isEmpty(pr.get("CID").toString())?"0":pr.get("CID").toString());
					detail.setLAC(StringUtils.isEmpty(pr.get("LAC").toString())?"0":pr.get("LAC").toString());
					detail.setLATB(StringUtils.isEmpty(pr.get("LATB")==null?"":pr.get("LATB").toString())?"0":pr.get("LATB").toString());
					detail.setLNGB(StringUtils.isEmpty(pr.get("LNGB")==null?"":pr.get("LNGB").toString())?"0":pr.get("LNGB").toString());
					detail.setADDRESS(StringUtils.isEmpty(pr.get("ADDRESS")==null?"":pr.get("ADDRESS").toString())?"0":pr.get("ADDRESS").toString());
					detail.setSERVICE_NUMBER(pr.get("SERVICE_NUMBER").toString());
					phoneMapDetailList.add(detail);
				}
			}
		}
        List<PhoneMapDetail> arrayList = new ArrayList<>(phoneMapDetailList);
		if(arrayList.size()>0) {
			List arrayList_hava = baseSite.getMap(arrayList);
			if(arrayList_hava!=null && arrayList_hava.size()>0) {
				for(PhoneMapDetail detail:(List<PhoneMapDetail>)arrayList_hava) {
					for(Map<String, Object> pr:list) {
						if(StringUtils.isNotEmpty(pr.get("SERVICE_NUMBER")==null?"":pr.get("SERVICE_NUMBER").toString()) 
								&& StringUtils.isEmpty(pr.get("ADDRESS")==null?"":pr.get("ADDRESS").toString()) 
								&& StringUtils.isNotEmpty(pr.get("CID")==null?"":pr.get("CID").toString()) 
								&& StringUtils.isNotEmpty(pr.get("LAC")==null?"":pr.get("LAC").toString())) {
							if((pr.get("CID").toString()).equals(detail.getCID()) && (pr.get("LAC").toString()).equals(detail.getLAC())) {
								pr.put("ADDRESS", detail.getADDRESS());
							}
						}
					}
				}
			}
		}
   }
}
